package com.linkwedo.saas.util.excel;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.linkwedo.saas.domain.Project;
import com.linkwedo.saas.domain.Target;
import com.linkwedo.saas.domain.Task;
import com.linkwedo.saas.domain.TaskUser;
import com.linkwedo.saas.domain.member.User;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelUtil {
	//文件路径  
	private String path;
	private String tableName;
	private String[] tableCols;

	//工作薄集合  
	private Workbook workbook;

	public ExcelUtil() {
	}

	public ExcelUtil(String path, String tableName, String[] tableCols)
			throws BiffException, IOException {
		this.tableName = tableName;
		this.tableCols = tableCols;
		this.setPath(path);
		this.setWorkbook(Workbook.getWorkbook(new java.io.File(path)));
	}

	/** 
	 * 获取工作薄数量 
	 * @return 工作薄数量 
	 */
	public int getNumberOfSheets(Workbook book) {
		return book == null ? 0 : book.getNumberOfSheets();
	}

	/** 
	 * 获取工作薄总行数 
	 * @param sheet 工作薄 
	 * @return 工作薄总行数 
	 */
	public int getRows(Sheet sheet) {
		return sheet == null ? 0 : sheet.getRows();
	}

	/** 
	 * 获取最大列数 
	 * @param sheet 工作薄 
	 * @return 总行数最大列数 
	 */
	public int getColumns(Sheet sheet) {
		return sheet == null ? 0 : sheet.getColumns();
	}

	/** 
	 * 获取每行单元格数组 
	 * @param sheet 工作薄 
	 * @param row 行数 
	 * @return 每行单元格数组 
	 */
	public Cell[] getRows(Sheet sheet, int row) {
		return sheet == null || sheet.getRows() < row ? null : sheet
				.getRow(row);
	}

	/** 
	 * 获取每行单元格数组 
	 * @param sheet 工作薄 
	 * @param endrow 结束行 
	 * @param endCol 结束列 
	 * @return 每行单元格数组 
	 */
	public Cell[][] getCells(Sheet sheet, int endrow, int endcol) {
		return getCells(sheet, 0, endrow, 0, endcol);
	}

	/** 
	 * 获取每行单元格数组 
	 * @param sheet 工作薄 
	 * @param startrow 行数 
	 * @param endrow 结束行 
	 * @param startcol 开始列 
	 * @param endCol 结束列 
	 * @return 每行单元格数组 
	 */
	public Cell[][] getCells(Sheet sheet, int startrow, int endrow,
			int startcol, int endcol) {
		Cell[][] cellArray = new Cell[endrow - startrow][endcol - startcol];
		int maxRow = this.getRows(sheet);
		int maxCos = this.getColumns(sheet);
		for (int i = startrow; i < endrow && i < maxRow; i++) {

			for (int j = startcol; j < endcol && j < maxCos; j++) {

				cellArray[i - startrow][j - startcol] = sheet.getCell(j, i);
			}

		}
		return cellArray;
	}

	/** 
	 * 得到行的值 
	 * @param sheet 
	 * @param col 
	 * @param startrow 
	 * @param endrow 
	 * @return 
	 */
	public Cell[] getColCells(Sheet sheet, int col, int startrow, int endrow) {
		Cell[] cellArray = new Cell[endrow - startrow];
		int maxRow = this.getRows(sheet);
		int maxCos = this.getColumns(sheet);
		if (col <= 0 || col > maxCos || startrow > maxRow || endrow < startrow) {
			return null;
		}
		if (startrow < 0) {
			startrow = 0;
		}
		for (int i = startrow; i < endrow && i < maxRow; i++) {
			cellArray[i - startrow] = sheet.getCell(col, i);
		}
		return cellArray;
	}

	/** 
	 * 得到列的值 
	 * @param sheet 
	 * @param row 
	 * @param startcol 
	 * @param endcol 
	 * @return 
	 */
	public Cell[] getRowCells(Sheet sheet, int row, int startcol, int endcol) {
		Cell[] cellArray = new Cell[endcol - startcol];
		int maxRow = this.getRows(sheet);
		int maxCos = this.getColumns(sheet);
		if (row <= 0 || row > maxRow || startcol > maxCos || endcol < startcol) {
			return null;
		}
		if (startcol < 0) {
			startcol = 0;
		}
		for (int i = startcol; i < startcol && i < maxCos; i++) {
			cellArray[i - startcol] = sheet.getCell(i, row);
		}
		return cellArray;
	}

	/** 
	 * 生成随机ID 
	 * @return 
	 */
	public static String getStrRandomId() {
		String uuid = UUID.randomUUID().toString().replace("-", "");
		return uuid;
	}

	/** 
	 * 组装SQL语句(扩展导入数据库额外增加字段的情况) 
	 * @param sheet 工作薄 
	 * @param startrow 开始行 
	 * @param endrow 结束行 
	 * @param startcol 开始列 
	 * @param endcol 结束列 
	 * @return SQL语句数组 
	 */
	public Object[] constrctCellsSql(Sheet sheet, int startrow, int endrow,
			int startcol, int endcol, String payTime) {
		Cell[][] cellArray = getCells(sheet, startrow, endrow, startcol, endcol);
		java.util.ArrayList<String> list = new java.util.ArrayList<String>();
		StringBuffer bf = new StringBuffer("INSERT INTO " + tableName + "(ID,");
		for (int i = 0; tableCols != null && i < tableCols.length; i++) {
			if (i != tableCols.length - 1)
				bf.append(tableCols[i]).append(",");
			else
				bf.append(tableCols[i]).append("");

		}
		bf.append(",PAY_TIME) VALUES ");
		for (int i = 0; i < cellArray.length; i++) {
			//在第一列前加个随机数列  
			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer.append(bf.toString() + "('" + getStrRandomId() + "',");
			Cell[] cell = cellArray[i];
			if (tableCols != null && cell != null
					&& tableCols.length != cell.length)
				continue;
			for (int j = 0; j < cell.length; j++) {
				String tmp = "";
				if (cell[j] != null && cell[j].getContents() != null) {
					tmp = (String) cell[j].getContents();
				}
				if (j != cell.length - 1)
					sqlBuffer.append("'").append(tmp).append("',");
				else
					sqlBuffer.append("'").append(tmp).append("'");
			}
			//增加时间字段  
			sqlBuffer.append(",").append(
					"to_date('" + payTime + "','YYYY-MM-DD HH24:MI:SS')");
			sqlBuffer.append(")");
			list.add(sqlBuffer.toString());
			System.out.println(sqlBuffer.toString());
		}
		System.out.println(list);
		return list.toArray();
	}

	/** 
	 * 获取Excel文件路径 
	 * @return Excel文件路径 
	 */
	public String getPath() {
		return this.path;
	}

	/** 
	 * 设置Excel文件路径 
	 * @param path Excel文件路径 
	 */
	public void setPath(String path) {
		this.path = path;
	}

	/** 
	 * 获取工作薄集合 
	 */
	public Workbook getWorkbook() {
		return this.workbook;
	}

	/** 
	 * 设置工作薄集合 
	 * @param workbook 工作薄集合 
	 */
	public void setWorkbook(Workbook workbook) {
		this.workbook = workbook;
	}

	/**
	 * 修改字符单元格的值
	 * @param dataSheet WritableSheet : 工作表
	 * @param col int : 列
	 * @param row int : 行
	 * @param str String : 字符
	 * @param format CellFormat : 单元格的样式
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void modiStrCell(WritableSheet dataSheet, int col, int row,
			String str, CellFormat format) throws RowsExceededException,
			WriteException {
		// 获得单元格对象
		WritableCell cell = dataSheet.getWritableCell(col, row);
		// 判断单元格的类型, 做出相应的转化
		if (cell.getType() == CellType.EMPTY) {
			Label lbl = new Label(col, row, str);
			if (null != format) {
				lbl.setCellFormat(format);
			} else {
				lbl.setCellFormat(cell.getCellFormat());
			}
			dataSheet.addCell(lbl);
		} else if (cell.getType() == CellType.LABEL) {
			Label lbl = (Label) cell;
			lbl.setString(str);
		} else if (cell.getType() == CellType.NUMBER) {
			// 数字单元格修改
			Number n1 = (Number) cell;
			n1.setValue(42.05);
		}
	}

	/**
	 * 修改数字单元格的值
	 * @param dataSheet WritableSheet : 工作表
	 * @param col int : 列
	 * @param row int : 行
	 * @param num double : 数值
	 * @param format CellFormat : 单元格的样式
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void modiNumCell(WritableSheet dataSheet, int col, int row,
			double num, CellFormat format) throws RowsExceededException,
			WriteException {
		// 获得单元格对象
		WritableCell cell = dataSheet.getWritableCell(col, row);
		// 判断单元格的类型, 做出相应的转化
		if (cell.getType() == CellType.EMPTY) {
			Number lbl = new Number(col, row, num);
			if (null != format) {
				lbl.setCellFormat(format);
			} else {
				lbl.setCellFormat(cell.getCellFormat());
			}
			dataSheet.addCell(lbl);
		} else if (cell.getType() == CellType.NUMBER) {
			// 数字单元格修改
			Number lbl = (Number) cell;
			lbl.setValue(num);
		} else if (cell.getType() == CellType.LABEL) {
			Label lbl = (Label) cell;
			lbl.setString(String.valueOf(num));
		}
	}

	/**
	 * 修改公式单元格的值
	 * @param dataSheet WritableSheet : 工作表
	 * @param col int : 列
	 * @param row int : 行
	 * @param startPos int : 开始位置
	 * @param endPos int : 结束位置
	 * @param format
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private void modiFormulaCell(WritableSheet dataSheet, int col, int row,
			int startPos, int endPos, CellFormat format)
			throws RowsExceededException, WriteException {
		String f = getFormula(col, row, startPos, endPos);
		// 插入公式（只支持插入，不支持修改）
		WritableCell cell = dataSheet.getWritableCell(col, row);
		if (cell.getType() == CellType.EMPTY) {
			// 公式单元格
			Formula lbl = new Formula(col, row, f);
			if (null != format) {
				lbl.setCellFormat(format);
			} else {
				lbl.setCellFormat(cell.getCellFormat());
			}
			dataSheet.addCell(lbl);
		} else if (cell.getType() == CellType.STRING_FORMULA) {
			//YTLogger.logWarn("Formula modify not supported!");
		}
	}

	/**
	 * 得到公式
	 * @param col int : 列
	 * @param row int : 行
	 * @param startPos int : 开始位置
	 * @param endPos int : 结束位置
	 * @return String
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	private String getFormula(int col, int row, int startPos, int endPos)
			throws RowsExceededException, WriteException {
		char base = 'A';
		char c1 = base;
		StringBuffer formula = new StringBuffer(128);
		// 组装公式
		formula.append("SUM(");
		if (col <= 25) {
			c1 = (char) (col % 26 + base);
			formula.append(c1).append(startPos).append(":").append(c1).append(
					endPos).append(")");
		} else if (col > 25) {
			char c2 = (char) ((col - 26) / 26 + base);
			c1 = (char) ((col - 26) % 26 + base);
			formula.append(c2).append(c1).append(startPos).append(":").append(
					c2).append(c1).append(endPos).append(")");
		}

		return formula.toString();
	}

	/**
	 * 根据模板文件创建并生成新的文件并另存
	 */
	public void generateNewFile(String templatePath, String targetPath,
			Project curProject) {
		try {
			//Excel获得文件 
			BufferedInputStream is = null;
			try {
				is = new BufferedInputStream(new FileInputStream(new File(
						templatePath)));
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			//System.out.println(is.read());
			//WorkbookSettings ws = new WorkbookSettings();    		
			Workbook wb = Workbook.getWorkbook(is);
			//打开一个文件的副本，并且指定数据写到目标文件 
			WritableWorkbook book = Workbook.createWorkbook(
					new File(targetPath), wb);
			//获得一个工作表 
			WritableSheet sheet = book.getSheet(0);
			System.out.println("行数：" + sheet.getRows() + "列数："
					+ sheet.getColumns());
			System.out.println("当前行：" + sheet.getCell(6, 2).getContents());
			//项目领导
			modiStrCell(sheet, 6, 1, curProject.getLeaderName(), null);
			//项目名称
			modiStrCell(sheet, 11, 1, curProject.getName(), null);
			//项目日期格式Date: mm/dd/yy
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
			String dateStr = sdf.format(curProject.getDueDate());		
			System.out.println(dateStr);
			modiStrCell(sheet, 25, 1, "Date："+dateStr, null);//项目名称
			modiStrCell(sheet, 5, 2, "项目目标:"+curProject.getDescription(), null);//项目目标
			
			/**项目概述第5列，49到52行*/
			for(int i =49;i<50;i++){
				modiStrCell(sheet, 5, i, curProject.getDescription(), null);//项目概述
			}
			
			/**任务处理 :第6列*/
			List<Task> taskList = curProject.getTasklist();
//			for(int i=5;i<8;i++){//i<8&&
			for(int i=5,j=0;j<taskList.size();i++,j++){
				Task task = taskList.get(j);
				modiStrCell(sheet, 6, i, task.getName(), null);//项目任务
			}
			int taskNum = taskList.size();
			/**目标处理：第36行第1到4列*/
			List<Target> targetList = curProject.getTargetlist();
			for(int j=1,k=0;j<4&&k<targetList.size();j++,k++){
				Target target = targetList.get(k);
				modiStrCell(sheet, j, 36, target.getName(), null);//项目领导
			}
			int targetNum = targetList.size();
			/**任务和目标关联1-4列，5-24行*/
			for(int i=5,k=0;i<(5+taskNum)&&k<taskNum;i++,k++){
				Task curTask = taskList.get(k);
				for(int j=1,n=0;j<4&&n<targetNum;j++,n++){	
					Target curTarget = targetList.get(n);
					
					if(curTask.getTargetList().contains(curTarget)){
						modiStrCell(sheet, j, i,"O", null);//项目领导
					}
				}
			}
			
			/**负责人：第36行*/
			List<User> userList = curProject.getUserlist();
			for(int j=21,k=0;j<25&&k<userList.size();j++,k++){
				User user = userList.get(k);
				modiStrCell(sheet, j, 36, user.getLastName()+user.getFirstName(), null);//项目领导
			}
			int userNum = userList.size();
			
			/**任务和时间关联9-20列，5-24（任务数）行*/
			for(int i=5;i<20;i++){
				for(int j=9;j<20;j++){
					modiStrCell(sheet, j, i,"O", null);//项目领导
				}
			}
			
			/**任务和负责人关联21-25列，5-24行*/
//			for(int i=5,k=0;i<(5+taskNum)&&k<taskNum;i++,k++){
//				Task curTask = taskList.get(k);
//				List<TaskUser> taskUsers = curTask.getTaskusers();
//				for(TaskUser curTaskUser:taskUsers){
//				  for(int j=21,n=0;j<25&&n<userNum;j++,n++){
//					User curUser = userList.get(n);
//					if(curTaskUser.getUser().equals(curUser)){
//						modiStrCell(sheet, j, i,"O", null);//项目领导
//					}
//				  }
//				}
//			}
				
			book.write();
			is.close();
			book.close();
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	/**
	 * Web response 导出文档
	 * @param response 
	 * @param expfilepath 需要导出文件的服务器路径
	 * @param expfilename 需要导出文件在客户端的文件名
	 */
	public void respsonseExcel(HttpServletResponse response,
			String expfilepath, String expfilename) {
		try {
			File file = new File(expfilepath);
			FileInputStream is = new FileInputStream(file);
			BufferedInputStream bis = null;

			BufferedOutputStream bos = null;

			// 保存本地文件名
			int fileSize = (int) file.length();
			String filename = URLEncoder.encode(expfilename, "UTF-8"); //中文文件名(乱码)转换   
			// 设置下载文件使用的报头域
			response.reset();
			response.setContentType("application/msexcel");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ filename);
			response.setContentLength(fileSize);
			// 得到响应对象的输出流，用于向客户端输出二进制数据
			ServletOutputStream sos = response.getOutputStream();
			bis = new BufferedInputStream(is);

			bos = new BufferedOutputStream(sos);//response.getOutputStream()

			byte[] buff = new byte[2048];

			int bytesRead;

			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);
			}

			bos.flush();
			bos.close();
			bis.close();
//			file.delete();//导出文档，删除原文件
			//logger.debug("JavaToMSWordManager.respsonseExpWord(response,expfilepath:"+expfilepath+",  expfilename:"+expfilename+"):response Web 响应导出文档成功！");
		} catch (Exception e) {
			// TODO: handle exception
			//logger.error("JavaToMSWordManager.respsonseExpWord(response,expfilepath:"+expfilepath+",  expfilename:"+expfilename+"):response Web 响应导出文档出错，错误信息："+e.getMessage());
		}
	}


	public void close() {
		if (workbook != null) {
			workbook.close();
		}
	}

	/** 
	 *  
	 * @param args 
	 */
	public static void main(String[] args) {
		try {
			//            File fileWrite = new File("c:/testWrite.xls");  
			//                    fileWrite.createNewFile();  
			//                OutputStream os = new FileOutputStream(fileWrite);  
			//                //ExcelUtil.writeExcel(os);  
			String sourceFile = "E:/one_page_project_template.xls";
			String newFile = "E:/one_page_project_1.xls";
			//             File file = new File("E:/template.xls");  
			//             if(file.exists()){ 
			//            	 System.out.println( "   file.exists   is   "+file.exists()); 
			//            	 }else{ 
			//            	 System.out.println( "   file.exists   is   "+file.exists()); 
			//
			//            	 } 
			ExcelUtil excelOp = new ExcelUtil();
			excelOp.generateNewFile(sourceFile, newFile, new Project());
		} catch (Exception e) {
			// TODO Auto-generated catch block  
			e.printStackTrace();
		}
	}
}
